﻿#region Librerias
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
#endregion

/// <summary>
/// Class News, define las noticias para el apartado tipo blog
/// </summary>

#region Class

public class clsNews : DAC
{
    #region Variables

    private int idNews;
    private String sTitle, sDescription, sSource, sSite, dtDate, sUrlImage;
    private const String tabla = "dbo.tblNews";

    #endregion

    #region Builder

    /// <summary>
    /// Constructor
    /// </summary>
    public clsNews() { }

    #endregion

    #region Methods

    #region Consultations

    public DataSet SeeAllTheRecords(){
        String query = "SELECT row_number() over(order by " + tabla + ".idNews) As NUMBER, ";
        query += tabla + ".idNews As ID, ";
        query += tabla + ".sTitle As TITLE, " + tabla + ".sDescription As DESCRIPTION, ";
        query += tabla + ".sSource As SOURCE, " + tabla + ".sSite As SITE, ";
        query += tabla + ".dtDate As DATE, " + tabla + ".sUrlImage As URLIMAGE ";
        query += "FROM " + tabla +" ";
        query += "ORDER BY " + tabla + ".idNews DESC";
        return this.ExecuteQuery(query);
    }

    public DataSet SeeAllTheRecordsDesc(){
        String query = "SELECT row_number() over(order by " + tabla + ".idNews) As NUMBER, ";
        query += tabla + ".idNews As ID, ";
        query += tabla + ".sTitle As TITLE, " + tabla + ".sDescription As DESCRIPTION, ";
        query += tabla + ".sSource As SOURCE, " + tabla + ".sSite As SITE, ";
        query += tabla + ".dtDate As DATE, " + tabla + ".sUrlImage As URLIMAGE ";
        query += "FROM " + tabla +" ";
        query += "ORDER BY " + tabla + ".idNews DESC";
        return this.ExecuteQuery(query);
    }

    public DataSet SeeAllTheRecordsFive()
    {
        String query = "SELECT top 4 ";
        query += tabla + ".idNews As ID, ";
        query += tabla + ".sTitle As TITLE ";
        query += "FROM " + tabla + " ";
        query += "ORDER BY " + tabla + ".idNews DESC";
        return this.ExecuteQuery(query);
    }

    public DataSet SeeAllTheRecords(int startRecord, int maxRecord, String text)
    {
        String query = "SELECT row_number() over(order by " + tabla + ".idNews) As NUMBER, ";
        query += tabla + ".idNews As ID, ";
        query += tabla + ".sTitle As TITLE, " + tabla + ".sDescription As DESCRIPTION, ";
        query += tabla + ".sSource As SOURCE, " + tabla + ".sSite As SITE, ";
        query += tabla + ".dtDate As DATE, " + tabla + ".sUrlImage As URLIMAGE ";
        query += "FROM " + tabla;
        if (!text.Equals(""))
        {
            query += " WHERE " + tabla + ".sTitle LIKE '%" + text + "%' ";
            query += "OR " + tabla + ".sDescription LIKE '%" + text + "%'";
        }
        query += " ORDER BY " + tabla + ".idNews DESC";
        if (startRecord == 0 && maxRecord == 0) {
            return this.ExecuteQuery(query);
        }
        else {
            return this.ExecuteQuery(query, startRecord, maxRecord);
        }
    }

    public DataSet ConsultID(String id)
    {
        String query = "SELECT ";
       query += tabla + ".idNews As ID, ";
        query += tabla + ".sTitle As TITLE, " + tabla + ".sDescription As DESCRIPTION, ";
        query += tabla + ".sSource As SOURCE, " + tabla + ".sSite As SITE, ";
        query += tabla + ".dtDate As DATE, " + tabla + ".sUrlImage As URLIMAGE ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idNews =" + id;
        return this.ExecuteQuery(query);
    }

    #endregion

    public int Insert()
    {
        String query = "INSERT INTO " + tabla;
        query += "(sTitle, sDescription, sSource, sSite, dtDate, sUrlImage) ";
        query += "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')";

        String sql = String.Format(query, this.STitle, this.SDescription, this.SSource, this.SSite, this.DtDate, this.SUrlImage);
        return this.ExecuteDML(sql, "no");
    }

    public int Update() {
        String query = "UPDATE " + tabla + " SET ";
        query += tabla + ".sTitle='" + this.STitle + "', " + tabla + ".sDescription='" + this.SDescription + "', ";
        query += tabla + ".sSource='" + this.SSource + "', " + tabla + ".sSite='" + this.SSite + "', ";
        query += tabla + ".dtDate='" + this.DtDate + "', " + tabla + ".sUrlImage='" + this.SUrlImage + "' ";
        query += "WHERE ";
        query += tabla + ".idNews=" + this.ID;
        return this.ExecuteDML(query, "no");
    }

    public int Delete()
    {
        String query = "DELETE FROM " + tabla + " WHERE " + tabla + ".idNews={0}";
        String sql = String.Format(query, this.ID);
        return this.ExecuteDML(sql, "no");
    }

    #endregion

    #region Setters and Getters
      
    public int ID
    {
        get { return idNews; }
        set { idNews = value; }
    }

    public String SUrlImage
    {
        get { return sUrlImage; }
        set { sUrlImage = value; }
    }

    public String SSource
    {
        get { return sSource; }
        set { sSource = value; }
    }

    public String SDescription
    {
        get { return sDescription; }
        set { sDescription = value; }
    }

    public String STitle
    {
        get { return sTitle; }
        set { sTitle = value; }
    }

    public String DtDate {
        get { return dtDate; }
        set { dtDate = value; }
    }

     public String SSite {
        get { return sSite; }
        set { sSite = value; }
    }    

    #endregion
     
}

#endregion